package com.turing.post.antimoney.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.antimoney.util.AlarmInfo;
import com.turing.post.antimoney.util.AlarmInfoDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 预警信息业务逻辑实现
 * 
 * @author 李治
 * 
 */
@Remote
@Local
@Stateless(mappedName = "antimoney.impl.AlarmInfoDaoImpl")
public class AlarmInfoDaoImpl implements AlarmInfoDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public List<AlarmInfo> getAlarmInfoByCondition(PCPSWebInput webInput,
			String startAlarmTime, String endAlarmTime, String dealStatus,
			Integer pageNo) {
		String issceCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		if ("".equals(issceCompanyNo)) {
			return null;
		}
		String sqlAlarmTime = "";
		if (PCPSUtil.isEmpty(startAlarmTime)) {
			startAlarmTime = PCPSUtil.getStringYMDByDate(new Date());
		}
		sqlAlarmTime += " and (a.alarmtime is null or a.alarmtime>=to_date('"
				+ startAlarmTime + " 00:00:00','yyyy-MM-dd hh24:mi:ss')) ";
		if (PCPSUtil.isEmpty(endAlarmTime)) {
			endAlarmTime = PCPSUtil.getStringYMDByDate(new Date());
		}
		sqlAlarmTime += " and (a.alarmtime is null or a.alarmtime<=to_date('"
				+ endAlarmTime + " 23:59:59','yyyy-MM-dd hh24:mi:ss')) ";
		String sql = "select * from alarminfo a where a.issuecompanyno ='"
				+ issceCompanyNo + "' " + sqlAlarmTime;
		if (!PCPSUtil.isEmpty(dealStatus))
			sql += " and a.dealstatus='" + dealStatus + "'";
		sql += " order by a.alarminfoid";
		Query query = _em.createNativeQuery(sql, AlarmInfo.class);
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * pageNo;
		query.setFirstResult(startPosition);
		query.setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public AlarmInfo getAlarmInfoById(BigInteger alarmInfoId) {
		return _em.find(AlarmInfo.class, alarmInfoId);
	}

	@Override
	public Integer getAlarmInfoCountByCondition(PCPSWebInput webInput,
			String startAlarmTime, String endAlarmTime, String dealStatus) {
		String issceCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sqlAlarmTime = "";
		if (PCPSUtil.isEmpty(startAlarmTime)) {
			startAlarmTime = PCPSUtil.getStringYMDByDate(new Date());
		}
		sqlAlarmTime += " and (a.alarmtime is null or a.alarmtime>=to_date('"
				+ startAlarmTime + " 00:00:00','yyyy-MM-dd hh24:mi:ss')) ";
		if (PCPSUtil.isEmpty(endAlarmTime)) {
			endAlarmTime = PCPSUtil.getStringYMDByDate(new Date());
		}
		sqlAlarmTime += " and (a.alarmtime is null or a.alarmtime<=to_date('"
				+ endAlarmTime + " 23:59:59','yyyy-MM-dd hh24:mi:ss')) ";
		String sql = "select nvl(count(*),0) from alarminfo a where a.issuecompanyno ='"
				+ issceCompanyNo + "' " + sqlAlarmTime;
		if (!PCPSUtil.isEmpty(dealStatus))
			sql += " and a.dealstatus='" + dealStatus + "'";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AlarmInfo> getAllAlarmInfoByIssueCompanyNo(String issceCompanyNo) {
		String sql = "select * from alarminfo a where a.issuecompanyno=?1";
		Query query = _em.createNativeQuery(sql, AlarmInfo.class).setParameter(
				1, issceCompanyNo);
		return query.getResultList();
	}

	@Override
	public BigInteger getMaxAlarmInfoId() {
		Query query = _em
				.createNativeQuery("select nvl(max(AlarmInfoId),0)+1 AlarmInfoId from AlarmInfo t");
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertAlarmInfo(AlarmInfo alarmInfo)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, alarmInfo);
	}

	@Override
	public void updateAlarmInfo(AlarmInfo alarmInfo)
			throws PCPSApplicationException {
		GeneralDao.update(_em, alarmInfo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AlarmInfo> getAlarmInfoReportListByCondition(
			PCPSWebInput webInput, BigInteger alarmRuleId,
			String startAlarmTime, String endAlarmTime, Integer page) {
		String issceCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String alarmRuleIdCond = "";
		if (alarmRuleId != null) {
			if (!alarmRuleId.equals(BigInteger.ZERO))
				alarmRuleIdCond = " and a.AlarmRuleId=" + alarmRuleId;

		}
		String startAlarmTimeCond = "";
		if (!PCPSUtil.isEmpty(startAlarmTime)) {
			startAlarmTimeCond += " and a.DealTime>=to_date('" + startAlarmTime
					+ " 00:00:00','yyyy-MM-dd hh24:mi:ss')";
		}
		String endAlarmTimeCond = "";
		if (!PCPSUtil.isEmpty(endAlarmTime)) {
			endAlarmTimeCond += " and a.DealTime<=to_date('" + endAlarmTime
					+ " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
		}
		String sql = "select a.* from AlarmInfo a where a.alarminfoid in (select s.alarminfoid from suspicioustrans s where (s.reportId =0 or s.reportId is null) ) "
				+ "and a.DealStatus='1' and a.NormalOrSuspicious='1' and a.IssueCompanyNo='"
				+ issceCompanyNo
				+ "' and a.DealTime is not null"
				+ alarmRuleIdCond + startAlarmTimeCond + endAlarmTimeCond;
		sql += " order by a.alarminfoid";
		Query query = _em.createNativeQuery(sql, AlarmInfo.class);
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		query.setFirstResult(startPosition);
		query.setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public Integer getAlarmInfoReportTotalByCondition(PCPSWebInput webInput,
			BigInteger alarmRuleId, String startAlarmTime, String endAlarmTime) {
		String issceCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String alarmRuleIdCond = "";
		if (alarmRuleId != null) {
			if (!alarmRuleId.equals(BigInteger.ZERO))
				alarmRuleIdCond = " and a.AlarmRuleId=" + alarmRuleId;
		}
		String startAlarmTimeCond = "";
		if (!PCPSUtil.isEmpty(startAlarmTime)) {
			startAlarmTimeCond += " and a.DealTime>=to_date('" + startAlarmTime
					+ " 00:00:00','yyyy-MM-dd hh24:mi:ss')";
		}
		String endAlarmTimeCond = "";
		if (!PCPSUtil.isEmpty(endAlarmTime)) {
			endAlarmTimeCond += " and a.DealTime<=to_date('" + endAlarmTime
					+ " 23:59:59','yyyy-MM-dd hh24:mi:ss')";
		}
		String sql = "select nvl(count(*),0) from AlarmInfo a where a.alarminfoid in (select s.alarminfoid from suspicioustrans s where (s.reportId =0 or s.reportId is null)) "
				+ "and a.DealStatus='1' and a.NormalOrSuspicious='1' and a.IssueCompanyNo='"
				+ issceCompanyNo
				+ "' and a.DealTime is not null"
				+ alarmRuleIdCond + startAlarmTimeCond + endAlarmTimeCond;
		sql += " order by a.alarminfoid";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 预付卡业务风险预警表
	 * 
	 * @param webInput
	 * @param branchno
	 *            预警机构
	 * @param startAlarmTime
	 *            预警起始时间
	 * @param endAlarmTime
	 *            预警终止时间
	 * @return list对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getAlarmInfoReport(PCPSWebInput webInput,
			Date startAlarmTime, Date endAlarmTime, String dealStatus,
			String normalOrSuspicious) {
		if (webInput == null || startAlarmTime == null || endAlarmTime == null)
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select a.AlarmTime,a.BranchName,a.BranchNo,a.RiskCustomName,b.BusinessType,a.AlarmReason,a.DealResult,a.DealOperId,a.normalorsuspicious,a.Remark,a.DealStatus from AlarmInfo a,AlarmInfoDetail b  where a.AlarmInfoId=b.AlarmInfoId "
				+ " and  a.AlarmTime>=?1 and a.AlarmTime<=?2 and a.issuecompanyno=?3 and (dealStatus =?4 or ?4 is null) and (normalOrSuspicious =?5 or ?5 is null) order by a.AlarmInfoId,b.seqno asc ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startAlarmTime);
		query.setParameter(2, endAlarmTime);
		query.setParameter(3, issuecompanyno);
		query.setParameter(4, dealStatus);
		query.setParameter(5, normalOrSuspicious);
		List<Object[]> result = query.getResultList();
		return result;
	}

	/**
	 * 可疑交易报表--报表
	 * 
	 * @param webInput
	 * @param branchno
	 *            预警机构
	 * @param startTranTime
	 *            交易起始时间
	 * @param endTranTime
	 *            交易终止时间
	 * @return list对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getSuspiciousReportList(PCPSWebInput webInput,
			Date startTranTime, Date endTranTime) {
		if (webInput == null || startTranTime == null || endTranTime == null)
			return null;
		String IssueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select b.branchname, b.issuecompanyno,a.tstm, a.CRTP,a.CRAT, a.TSDR, a.CRPP,a.TCIP, a.TMNM, a.CTTP, a.TICD, a.CTNM, a.CTAR,a.CCTL,a.CEML,"
				+ " a.CITP,a.CTID,a.CBAT,a.CBAC,a.CABM,a.CTVC,a.CRNM,a.CRIT,a.CRID,a.TCNM,a.TCIT,a.TCID,a.TCAT,a.TCBA,a.TCBN,d.RFSG,a.CRAT,d.SSDS,d.SSTM,b.Remark "
				+ " from SuspiciousTrans a ,AlarmInfo b,AlarmInfoDetail c,SuspiciousReport d,AlarmRule e "
				+ " where a.alarminfoid=b.alarminfoid and b.alarminfoid=c.alarminfoid  and a.reportid=d.reportid and d.IssueCompanyNo=?3  "
				+ "  and b.issuecompanyno=d.IssueCompanyNo and e.alarmruleid=b.alarmruleid and e.issuecompanyno=b.issuecompanyno"
				+ " and b.AlarmTime>=?1 and b.AlarmTime<=?2 order by a.AlarmInfoId,c.seqno asc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startTranTime);
		query.setParameter(2, endTranTime);
		query.setParameter(3, IssueCompanyNo);
		List<Object[]> result = query.getResultList();
		return result;
	}

	/**
	 * 日常可疑交易报表--报表
	 * 
	 * @param webInput
	 * @param branchno
	 *            预警机构
	 * @param startTranTime
	 *            交易起始时间
	 * @param endTranTime
	 *            交易终止时间
	 * @return list对象
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getSuspiciousReportDayList(PCPSWebInput webInput,
			Date startTranTime, Date endTranTime) {
		if (webInput == null || startTranTime == null || endTranTime == null)
			return null;

		String IssueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select b.issuecompanyno, b.branchname,a.tstm,a.CRAT,a.TSDR,a.CRPP,a.TCIP,a.TMNM,a.CTTP,a.CTNM,a.CITP,"
				+ " a.CTID,a.CBAT,a.TCNM,a.TCIT,a.TCID,a.CRAT,d.SSDS,d.SSTM,b.Remark"
				+ " from SuspiciousTrans a ,AlarmInfo b,AlarmInfoDetail c,SuspiciousReport d,AlarmRule e "
				+ " where a.alarminfoid=b.alarminfoid and b.alarminfoid=c.alarminfoid  and a.reportid=d.reportid and d.IssueCompanyNo=?3  "
				+ "  and b.issuecompanyno=d.IssueCompanyNo and e.alarmruleid=b.alarmruleid and e.issuecompanyno=b.issuecompanyno"
				+ " and b.AlarmTime>=?1 and b.AlarmTime<=?2 order by a.AlarmInfoId,c.seqno asc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startTranTime);
		query.setParameter(2, endTranTime);
		query.setParameter(3, IssueCompanyNo);
		List<Object[]> result = query.getResultList();
		return result;
	}

	@Override
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void removeAlarmInfoByInfoId(String infoId) {
		String sql = "delete alarminfo t where t.alarminfoid = ?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, infoId);

		query.executeUpdate();
	}
}
